IF OBJECT_ID('dbo.RptAwardedBudgetsCostSharing') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptAwardedBudgetsCostSharing
    IF OBJECT_ID('dbo.RptAwardedBudgetsCostSharing') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptAwardedBudgetsCostSharing >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptAwardedBudgetsCostSharing >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptAwardedBudgetsCostSharing
--
-- Author:      Chris Bernad, Parallax Consulting LLC
--
-- Create date: 12/4/2008
--
-- Description: Source of Report, "AwardedBudgetsCostSharing"
--                  In Insight DB Reporting Services
--
-- Parameters:  
--     @Institution             
--     @Department              
--     @PrincipalInvestigator        
--     @SponsorType                 
--     @CostShareTypoe       
--     @domainUserId            
--              
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- 
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 12/4/08     CB        Created
-- =======================================================================
CREATE PROCEDURE dbo.RptAwardedBudgetsCostSharing 
     (
     @Institution             VARCHAR(100) = NULL,
     @Department              VARCHAR(200) = NULL,
     @PrincipalInvestigator   VARCHAR(2000) = NULL,    
     @Sponsor                 VARCHAR(2000) = NULL,      
     @SponsorType             VARCHAR(200) = NULL,
     @CostShareType			  VARCHAR(100) = NULL,
     @DomainUserId            INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error INT,
               @RowCount INT
               
     -- ALL checking
     IF @Institution IN ('ALL','','0') SET @Institution = NULL
     IF @Department IN ('ALL','','0') SET @Department = NULL
     IF @PrincipalInvestigator IN ('ALL','','0') SET @PrincipalInvestigator = NULL
     IF @Sponsor IN ('ALL','','0') SET @Sponsor = NULL
     IF @SponsorType IN ('ALL','','0') SET @SponsorType = NULL
     IF @CostShareType IN ('ALL','','0') SET @CostShareType = NULL
          
     -- Result Set
     DECLARE   @Proposal TABLE (InstCode varchar(10),
                                PropNo varchar(10))
     INSERT INTO @Proposal
     
     SELECT p.InstCode,p.PropNo
     FROM vwMasterProposalsInfo p
	      INNER JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements(@DomainUserId) sec ON p.propNo = sec.InfoEdPropNumber
	 WHERE p.PropStat ='PARTNERS_PD_PROP_STAT_2' AND --Fully Executed/Awarded - Activated 
	   (p.VoluntaryCommitted= 1 OR p.Mandatory = 1 OR p.MandatoryandVoluntaryCommitted = 1) AND
	   (@Institution IS NULL OR  p.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institution))) AND
       (@Department IS NULL OR p.DeptartmentChiefCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Department))) AND
       (@PrincipalInvestigator IS NULL OR p.PISearchName IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @PrincipalInvestigator ))) AND
       (@Sponsor IS NULL OR p.SponCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @Sponsor ))) AND
       (@SponsorType IS NULL OR p.SponsorTypeCode IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode( @SponsorType ))) 	     
	    
     
     
    SELECT  
		p.ProposalNumber,
		bud.PSFundNumber,
		p.SponsorAgreementId,
		p.Institution, 
	    p.DeptartmentChiefCode,p.Department,
	    p.UnitChiefCode,p.Unit,
        p.PrincipalInvestigator,  
		p.SponsorName, p.SponsorType,
		p.ProjectStartDate,
		p.ProjectEndDate,
		VoluntaryCommitted,
		Mandatory,
		MandatoryandVoluntaryCommitted,
		bud.CostShareType,
		bud.CostSharingAmount
	FROM vwMasterProposalsInfo p
	     INNER JOIN @Proposal pf ON p.PropNo = pf.PropNo
	     INNER JOIN
	     (SELECT b.prop_no,b.hacct AS PSFundNumber,MIN(long_name) AS CostShareType,
	            SUM(ISNULL(s.amt,0)) AS CostSharingAmount
	       FROM @Proposal pbf 	     
			 INNER JOIN	pd_grc_bgt b ON pbf.PropNo = b.prop_no AND pbf.instcode = b.inst_code AND b.rec_type = '3'
			 INNER JOIN codetab c ON b.bud_stat = c.codeid  AND	c.arr_name = 'awd_stat' AND	c.code_desc = 'Awarded'
			 INNER join pt_trans_xref x ON b.bud_id = x.bud_id 
			 INNER JOIN pt_trans t ON x.budgetno = t.budgetno
			 INNER JOIN pt_trans_src s ON s.budgetno = t.budgetno AND t.trans_id = s.trans_id
			 INNER JOIN pt_trans_src_xref sx ON sx.inst_code = b.inst_code and sx.prop_no = b.prop_no and sx.source = s.source 
					   AND sx.long_name = 'institution' 
			 LEFT OUTER JOIN codetab cst ON s.COSTSHARET = cst.codeid AND cst.arr_name = 'COSTSHARET' 
			 LEFT OUTER JOIN codetab csc ON s.COSTSHAREC = csc.codeid AND csc.arr_name = 'COSTSHAREC' 
		  GROUP BY 	b.prop_no,b.hacct) bud ON p.PropNo = bud.prop_no
  ORDER BY p.Institution,p.ProposalNumber

    
  SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
  IF @Error!=0
     RETURN -1
      
  -- Return Success
  RETURN 0

     
END
GO

IF OBJECT_ID('dbo.RptAwardedBudgetsCostSharing') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptAwardedBudgetsCostSharing >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptAwardedBudgetsCostSharing>>>'
GO


IF  EXISTS
    (SELECT name
     FROM sys.database_principals
     WHERE name = 'InsightReport')
BEGIN
	GRANT EXECUTE ON dbo.RptAwardedBudgetsCostSharing TO InsightReport
END


GO



